# 这是一个示例 Python 脚本。

# 按 Shift+F10 执行或将其替换为您的代码。
# 按 双击 Shift 在所有地方搜索类、文件、工具窗口、操作和设置。
# 景气指数

import pandas as pd
import prophet
import pymysql
import sys
from datetime import datetime

field = []

year_on_year_growth_rate, mean_list, max_list, x_index, y_index, year_list, day = [], [], [], [], [], [], []  # 同比增长率


def yoy_growth(new, old):
    return (new - old) / old


def date_parse(x):
    return datetime.strptime(x, '%Y-%U-%w')


def data_predict(x):
    x = str(x)
    return datetime.strptime(x, '%Y-%m-%d %H:%M:%S').strftime("%Y-%W")


def date_week(x):
    return datetime.strptime(x, '%Y-%m-%d').strftime("%W")


def create_db():
    return pymysql.connect(host='47.94.200.147', port=3306, user='root', passwd='lsdl000423', database='ment',
                           charset="utf8")  # 建立数据库连接


def insert(sql):
    conn = create_db()  # 获取连接
    cur = conn.cursor()  # 获取操作游标

    try:
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def query(sql):
    conn = create_db()  # 获取连接
    cur = conn.cursor()  # 获取操作游标

    cur.execute(sql)

    results = cur.fetchall()

    return results


def delete(sql):
    conn = create_db()  # 获取连接
    cur = conn.cursor()  # 获取操作游标

    try:
        cur.execute(sql)
        conn.commit()
    except Exception as e:
        print(e)
        conn.rollback()


def dealData_db(filename):
    global year_list

    df=pd.read_csv(filename,header=0,names=['','year_id','month_id','景气指数'])
    df.to_csv(filename,index=0)

    x, y = 'year_id', '景气指数'
    sentiment = pd.read_csv(filename)

    sentiment['x_index'] = sentiment['year_id'].map(str) + "-" + sentiment['month_id'].map(str)

    year_list = sentiment['year_id'].drop_duplicates(keep='first').tolist()

    for i in year_list:
        mean_list.append(sentiment[sentiment['year_id'] == i].loc[:, '景气指数'].mean())  # 求各个年份的景气指数平均值
        max_list.append(sentiment[sentiment['year_id'] == i].loc[:, '景气指数'].max())  # 求各个年份的景气指数最大值

        index = sentiment[sentiment['year_id'] == i]
        x_index.append(index.sort_values('month_id')['x_index'].tolist())
        y_index.append(index.sort_values('month_id')['景气指数'].tolist())
    # 获取同比增长率
    for i in range(len(mean_list) - 1):
        year_on_year_growth_rate.append(yoy_growth(mean_list[i + 1], mean_list[i]))

    if query("select * from sentiment") is not None:
        delete("delete from sentiment where id = 1")

    sql = "insert into sentiment values (%d,\"%s\",'%s','%s','%s','%s',%d,'%s','%s')" % (
        1, x_index, str(y_index), str(mean_list), str(year_on_year_growth_rate), str(max_list), 0, str(year_list), None)
    sche_sql = "update schedule set percentage = %d , state = '%s'  where id = 1" % (25, "正在进行数据统计")
    insert(sche_sql)
    insert(sql)


def sent_predict(filename):
    sent = pd.read_csv(filename)

    sent['year_id'] = sent['year_id'].map(str) + "-" + sent['month_id'].map(str) + "-0"

    sent['year_id'] = sent['year_id'].map(date_parse)

    sent = sent.sort_values('year_id')

    sent = sent.rename(columns={'year_id': 'ds', '景气指数': 'y'})

    sent_pre = sent[['ds', 'y']]

    temp = sorted(set(sent_pre['ds'].apply(str).str[:4].tolist()))

    for i in temp:
        if i != temp[len(temp) - 1]:
            continue
        sent_mean = sent_pre[sent_pre['ds'].map(str).str[:4] == i]
        sent_std = sent_pre[sent_pre['ds'].map(str).str[:4] == i]
        sent_mean = sent_mean['y'].mean()
        sent_std = sent_std['y'].std()
        # 异常点
        sent_pre = sent_pre.drop(
            sent_pre[(sent_pre['y'] < sent_mean - 3 * sent_std) & (sent_pre['ds'].map(str).str[:4] == i)].index)
        sent_pre = sent_pre.drop(
            sent_pre[(sent_pre['y'] > sent_mean + 3 * sent_std) & (sent_pre['ds'].map(str).str[:4] == i)].index)

    m = prophet.Prophet(weekly_seasonality=True)
    m.fit(sent_pre)
    future = m.make_future_dataframe(periods=51, freq='W')
    future.tail()
    forecast = m.predict(future)

    forecast.loc[:, ['ds', 'yhat']]

    new_forecast = forecast[['ds', 'yhat']]  # .mul(sent_std).add(sent_mean)

    x_index.clear()

    new_forecast['ds'] = new_forecast['ds'].map(data_predict)

    year = sorted(set(new_forecast['ds'].str[:4].tolist()))

    new_year = year[len(year) - 1]

    new_forecast = new_forecast.loc[(new_forecast['ds'].str[:4] == year[len(year) - 1]) | (
            new_forecast['ds'].str[:4] == year[len(year) - 2]), :]

    mean_list.clear()
    max_list.clear()
    x_index.clear()
    y_index.clear()
    year_on_year_growth_rate.clear()

    for i in year[-2:]:
        x_index.append(new_forecast[new_forecast['ds'].str[:4] == i].loc[:, 'ds'].tolist())
        y_index.append(new_forecast[new_forecast['ds'].str[:4] == i].loc[:, 'yhat'].tolist())
        mean_list.append(new_forecast[new_forecast['ds'].str[:4] == i].loc[:, 'yhat'].mean())  # 求各个年份的景气指数平均值
        max_list.append(new_forecast[new_forecast['ds'].str[:4] == i].loc[:, 'yhat'].max())  # 求各个年份的景气指数最大值
        day.append(new_forecast[
                       (new_forecast['ds'].str[5:] == date_week(i + '-10-1')) & (new_forecast['ds'].str[:4] == i)].loc[
                   :,
                   'yhat'].values)
    mean_list.append(new_forecast[(new_forecast['ds'].str[:4] == new_year) & (
            new_forecast['ds'].str[5:] >= date_week(new_year + '-7-1')) & (
                                          new_forecast['ds'].str[5:] <= date_week(new_year + '-9-1'))].loc[:,
                     'yhat'].min())  # 暑运景气指数最小值
    month_year = int(new_forecast[new_forecast['yhat'] == max_list[1]].loc[:, 'ds'].str[5:].values)

    year_on_year_growth_rate.append(yoy_growth(max_list[0], mean_list[1]))
    year_on_year_growth_rate.append(float(yoy_growth(day[1], day[0])))

    if query("select * from sentiment") is not None:
        delete("delete from sentiment where id = 2")

    sql = "insert into sentiment values (%d,\"%s\",'%s','%s','%s','%s',%d,\"%s\",'%s')" % (
        2, str(x_index), str(y_index), str(mean_list),
        str(year_on_year_growth_rate), str(max_list), 1, str(year[-2:]), month_year)
    sche_sql = "update schedule set percentage = %d , state = '%s' where id = 1" % (35, "正在进行数据预测")
    insert(sche_sql)
    insert(sql)


def deal_sent():
    for row in query("select rate,sent_max from sentiment where state = 0"):
        rate = row[0]
        sent_max = row[1]
    rate = str(rate).replace('[', '')
    rate = str(rate).replace(']', '')

    sent_max = str(sent_max).replace('[', '')
    sent_max = str(sent_max).replace(']', '')

    list_rate = rate.split(", ")
    sent_max = sent_max.split(", ")

    new_rate = float(list_rate[len(list_rate) - 1])

    sub = float(list_rate[len(list_rate) - 1]) - float(list_rate[len(list_rate) - 2])

    if new_rate < 0:
        trend = '下降态势'
        senttiment = '逐步下降'
        if sub < 0:
            accelerate = '下滑较缓'
            same_time = "下降超同期" + sub + "百分点"
        else:
            accelerate = '下滑较快'
            same_time = "下降低于同期" + sub + "百分点"
    else:
        trend = '上升态势'
        senttiment = '稳步上升'
        if sub < 0:
            accelerate = '增速放缓'
            same_time = "增速低于同期" + str("%.4f%%" % (abs(sub) * 100)) + "百分点"
        else:
            accelerate = '增速加快'
            same_time = "增速超同期" + str("%.4f%%" % (abs(sub) * 100)) + "百分点"
    field.append(trend)
    field.append(accelerate)
    field.append(senttiment)
    field.append(sent_max[len(sent_max) - 1])
    field.append(str("%.4f%%" % (abs(new_rate) * 100)))
    field.append(same_time)

    if query("select * from sentfield") is not None:
        delete("delete from sentfield where id = 1")

    sql = "insert into sentfield(id,field0,field1,field2,field3,field4,field5) values (%d,'%s','%s','%s','%s','%s','%s')" % (
        1, field[0], field[1], field[2], field[3], field[4], field[5])
    sche_sql = "update schedule set percentage = %d , state = '%s' where id = 1" % (55, "正在进行字段填充")
    print(sche_sql)
    insert(sche_sql)
    insert(sql)


def deal_forecast():
    for row in query("select sent_mean,rate,sent_max,month_year,year from sentiment where state = 1"):
        sent_mean = row[0]
        rate = row[1]
        sent_max = row[2]
        month_year = row[3]
        year = row[4]

    rate = str(rate).replace('[', '')
    rate = str(rate).replace(']', '')
    list_rate = rate.split(", ")

    sent_mean = str(sent_mean).replace('[', '')
    sent_mean = str(sent_mean).replace(']', '')
    list_sent_mean = sent_mean.split(", ")

    year = str(year).replace('[', '')
    year = str(year).replace(']', '')
    list_year = year.split(", ")

    if float(list_rate[0]) > 0:
        trend = '稳步上升'
        min = list_sent_mean[2]
        day = str("%.4f%%" % (abs(float(list_rate[1])) * 100))
        month = int(month_year) / 4
        new_year = list_year[1].replace('\'', '')
        year = list_year[0].replace('\'', '')
    else:
        trend = '呈下降趋势'
        min = sent_mean[2]
        day = rate[1]
        month = month_year / 4
        year = year[0]

    sql = "update sentfield set field6 = '%s',field7 = '%s',field8 = '%s',field9 = '%s',field10 = '%s',field11 = '%s',field12 = '%s' where id = 1" % (
        new_year, trend, min, day, int(month), year, str("%.4f%%" % (abs(float(list_rate[0])) * 100)))
    sche_sql = "insert into schedule values (%d, %d , '%s')" % (5, 65, "正在进行字段填充")

    sche_sql = "update schedule set percentage = %d , state = '%s' where id = 1" % (65, "正在进行字段填充")

    insert(sche_sql)
    insert(sql)


if __name__ == '__main__':
    dealData_db(sys.argv[1])
    sent_predict(sys.argv[1])
    deal_sent()
    deal_forecast()
